package com.beordie.dao.impl;

import com.beordie.dao.IUsersDao;
import com.beordie.model.Role;
import com.beordie.model.Users;
import com.beordie.utils.DBUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @Classname UsersDaoImpl
 * @Description TODO
 * @Date 2021/6/8 12:25
 * @Created 30500
 */
public class UsersDaoImpl extends DBUtils implements IUsersDao {
    @Override
    public Users getByNamePassword(String loginName, String password) {
        String sql = "select * from users where loginname=? and password=?";
        List<String> list = new ArrayList();
        list.add(loginName);
        list.add(password);
        resultSet = super.query(sql, list);
        Users user = null;
        if (resultSet != null){
            try {
                while (resultSet.next()) {
                    user = new Users();
                    user.setUserId(resultSet.getInt("userid"));
                    user.setRealName(resultSet.getString("realname"));
                    user.setRoleId(resultSet.getInt("roleid"));
                    user.setLoginName(loginName);
                    user.setPassword("");
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            } finally {
                super.close();
            }
        }
        return user;
    }

    @Override
    public int getByAll() {
        String sql = "select count(*) from users u,role r where u.roleid=r.roleid";
        resultSet = super.query(sql, null);
        int result = 0;
        if (resultSet != null){
            try {
                while (resultSet.next()) {
                    result = resultSet.getInt(1);
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            } finally {
                close();
            }
        }
        return result;
    }

    @Override
    public List<Users> getByLimit(int index, int pageSize) {
        String sql = "select userid,loginname,realname,rolename from users u,role r where u.roleid=r.roleid limit ?,?";
        // 填充传递参数
        List parmas = new ArrayList<>();
        parmas.add((index - 1) * pageSize);
        parmas.add(pageSize);
        resultSet = super.query(sql, parmas);
        List<Users> users = new ArrayList<>();
        if (resultSet != null){
            try {
                while (resultSet.next()) {
                    // 填充用户信息
                    Users user = new Users();
                    Role role = new Role();
                    user.setUserId(resultSet.getInt("userid"));
                    user.setRealName(resultSet.getString("realname"));
                    user.setLoginName(resultSet.getString("loginname"));
                    role.setRoleName(resultSet.getString("rolename"));
                    user.setRole(role);
                    users.add(user);
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            } finally {
                super.close();
            }
        }
        return users;
    }

    @Override
    public int add(Users user) {
        // 构造 SQL 添加语句
        String sql = "insert into users (loginname,password,realname,sex,email,address,phone,cardid,description,roleid) " +
                "values(?,?,?,?,?,?,?,?,?,?)";
        List<Object> params = new ArrayList<>();
        params.add(user.getLoginName());
        params.add(user.getPassword());
        params.add(user.getRealName());
        params.add(user.getSex());
        params.add(user.getEmail());
        params.add(user.getAddress());
        params.add(user.getPhone());
        params.add(user.getCardId());
        params.add(user.getDescription());
        params.add(user.getRoleId());

        // 查询数据库结果
        int result = super.update(sql, params);
        return result;
    }

    @Override
    public int delete(int index) {
        // 构建 SQL 语句
        String sql = "delete from users where userid=?";
        List<Integer> params = new ArrayList<>();
        params.add(index);

        // 提交数据库信息
        int result = super.update(sql, params);
        return result;
    }

    @Override
    public int update(Users user) {
        // 构造 SQL 添加语句
        String sql = "update users set loginname=?,password=?,realname=?,sex=?," +
                "email=?,address=?,phone=?,cardid=?,description=?,roleid=? " +
                "where userid=?";
        List<Object> params = new ArrayList<>();
        params.add(user.getLoginName());
        params.add(user.getPassword());
        params.add(user.getRealName());
        params.add(user.getSex());
        params.add(user.getEmail());
        params.add(user.getAddress());
        params.add(user.getPhone());
        params.add(user.getCardId());
        params.add(user.getDescription());
        params.add(user.getRoleId());
        params.add(user.getUserId());

        // 提交数据库
        int result = super.update(sql, params);
        return result;
    }

    @Override
    public Users getByIndex(int userId) {
        String sql = "select * from users where userid=?";
        List<Integer> params = new ArrayList<>();
        params.add(userId);
        resultSet = super.query(sql, params);

        Users user = new Users();
        if (resultSet != null){
            try {
                while (resultSet.next()) {
                    // 填充用户信息
                    user.setUserId(resultSet.getInt("userid"));
                    user.setRealName(resultSet.getString("realname"));
                    user.setLoginName(resultSet.getString("loginname"));
                    user.setPassword(resultSet.getString("password"));
                    user.setSex(resultSet.getInt("sex"));
                    user.setEmail(resultSet.getString("email"));
                    user.setAddress(resultSet.getString("address"));
                    user.setPhone(resultSet.getString("phone"));
                    user.setCardId(resultSet.getString("cardid"));
                    user.setDescription(resultSet.getString("description"));
                    user.setRoleId(resultSet.getInt("roleid"));
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            } finally {
                super.close();
            }
        }
        return user;
    }


}
